﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports Entidad

Public Class NotaCreditoDetalleDatos

    Private Path As String = "C:\DATOS\PRUEBA\"
    Private Path1 As String = "C:\DATOS\CFG\"

    Function carpetaMensual() As String
        Dim sConn As String
        Dim mesanio As String = ""
        Dim mes As String = ""
        Dim anio As String = ""
        Dim sqlComm As OdbcDataAdapter

        Dim query As String = "SELECT meses,anhos FROM matrocfg WHERE codcia = '01'"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path1 + "MATROCFG.DBF") & ";"

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            dbConn.Open()
            sqlComm = New OdbcDataAdapter(query, dbConn)
            Dim dsOc As New Data.DataSet()
            sqlComm.Fill(dsOc)
            mes = dsOc.Tables(0).Rows(0)("meses").ToString.Trim
            anio = dsOc.Tables(0).Rows(0)("anhos").ToString.Trim
            mesanio = anio.Substring(2) & mes
            dbConn.Close()
        End Using

        Return mesanio
    End Function

    Function obtenerDetallesDBF(ByVal codNota As String) As DataSet
        Dim ds As New DataSet

        Dim sConn As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + carpetaMensual() + "\ARINGDO2.DBF") & ";"
        Dim query As String = "SELECT item,qenv,codalm FROM ARINGDO2 " &
                              "WHERE docto = '" & codNota & "' AND len(alltrim(item)) > 3"

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                Dim sqlComm As New OdbcDataAdapter(query, dbConn)
                sqlComm.Fill(ds)
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                dbConn.Close()
            End Try
        End Using

        Return ds
    End Function

    Sub registrarNotaCreditoDetalle(ByVal ncd As NotaCreditoDetalle)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "INSERT INTO Documento_Cliente_Detalle(CIA,SUC, " &
                              "TDOC_Codigo,DCI_Codigo,DCD_Codigo,DCD_Fecha, " &
                              "ART_Codigo,ALM_Codigo,DCD_Ordenado,DCD_Atendido, " &
                              "DCD_Descuento,DCD_ValorUnit,DCD_UltModificacion, " &
                              "DCD_DCDCodigo,DCD_DsctoAdicional,DCD_FechasLlegada, " &
                              "DCD_Activo) " &
                              "VALUES(@cia,@suc,@tipo,@dciCodigo,@dcdCodigo, " &
                              "null,@artCodigo,null,@cantidad,null,null, " &
                              "null,null,null,null,null,@act)"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@cia", ncd.CIA)
            .Parameters.AddWithValue("@suc", ncd.SUC)
            .Parameters.AddWithValue("@tipo", ncd.TIPO)
            .Parameters.AddWithValue("@dciCodigo", ncd.DCI_Codigo)
            .Parameters.AddWithValue("@dcdCodigo", ncd.DCD_Codigo)
            .Parameters.AddWithValue("@artCodigo", ncd.ART_Codigo)
            .Parameters.AddWithValue("@cantidad", ncd.DCD_Cantidad)
            .Parameters.AddWithValue("@act", ncd.ACTIVO)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function obtenerCodigoClienteDetalle(ByVal codNota As String, ByVal tdoc As String) As String
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT DCD_Codigo FROM Documento_Cliente_Detalle WHERE " &
                              "TDOC_Codigo = '" & tdoc & "' AND DCI_Codigo = '" & codNota & "'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds.Tables(0).Rows(0)("DCD_Codigo").ToString.Trim
    End Function

    Function cantidadDetalleCredito(ByVal codDoc As String, ByVal tdoc As String) As Integer
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT count(*) AS cont FROM Documento_Cliente_Detalle WHERE DCI_Codigo = '" & codDoc & "' AND " &
                              "TDOC_Codigo = '" & tdoc & "'"
        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return CInt(ds.Tables(0).Rows(0)("cont").ToString.Trim)
    End Function

End Class
